﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using micsun.Logic;
using micsun.Data;

namespace commfuncs.Search
{
    public class SearchBL
    {
        #region 一般查询使用...

        public DataTable GetSitTableListForDrop(int layID)
        {
            string strsql = "select * from t_site_tree where LayID="+layID;
            DataTable dt = new DataTable();
            micsunDB.CreateDataAdapter(strsql, null).Fill(dt);
            return dt;
        }

        public DataTable GetSitTableListForDrop(int layID , int parentTreeId )
        {
            string strsql = "select * from t_site_tree where LayID=" + layID + " and PapaID="+parentTreeId;
            DataTable dt = new DataTable();
            micsunDB.CreateDataAdapter(strsql, null).Fill(dt);
            return dt;
        }

        public DataTable GetSitTableListForDrop(string siteId)
        {
            string strsql = "select * from t_sensors where cbt='" + siteId + "'";
            DataTable dt = new DataTable();
            micsunDB.CreateDataAdapter(strsql, null).Fill(dt);
            return dt;
        }

        public DataTable GetSensorInfo(string ssrKey)
        {
            string strSql = "select * from t_sensors where SsrKey='" + ssrKey + "'";
            DataTable dt = new DataTable();
            micsunDB.CreateDataAdapter(strSql, null).Fill(dt);
            return dt;
        }

        public static string GetSpSsrStateDes(string ssrStateid)
        {
            return Misc.GetSpTextById(Convert.ToInt32(ssrStateid), "StateID", "StateName", "sp_ssrstate");
        }

        public static string GetSpRptStateDes(string rptId)
        {
            string strSql = "select max(SsrState) from t_sensors where STPID='" + rptId + "'";
            object obje = micsunDB.CreateDataAccessor().ExecuteScalar(strSql, null);
            int i = 0;
            if (obje != null)
            {
                i = Convert.ToInt32(i);
            }
            return Misc.GetSpTextById(i, "StateID", "StateName", "sp_ssrstate");
        }

        public static string GetSpSiteStateDes(string siteId, int layId)
        {
            string strSql = string.Empty;
            if (layId == 1)
            {
                string papaid = micsunDB.CreateDataAccessor().ExecuteScalar("select TreeID from t_site_tree where SiteID='" + siteId + "'", null).ToString();
                strSql = "select PapaID,LayID,SiteID from t_site_tree where PapaID=" + papaid;
                DataTable dt = new DataTable();
                micsunDB.CreateDataAdapter(strSql, null).Fill(dt);
                int max=0,ti= 0;
                for (int i = 0; i < dt.Rows.Count; i++ )
                {
                    strSql = "select max(SsrState) from t_sensors where cbt='" + dt.Rows[i]["SiteID"] + "'";
                    object obj = micsunDB.CreateDataAccessor().ExecuteScalar(strSql, null);
                    if (obj != DBNull.Value)
                    {
                        ti = Convert.ToInt32(obj);
                    }
                    if (max < ti)
                    {
                        max = ti;
                    }
                }
                return Misc.GetSpTextById(max, "StateID", "StateName", "sp_ssrstate");
            }
            else
            {
                strSql = "select max(SsrState) from t_sensors where cbt='" + siteId + "'";
                object obje = micsunDB.CreateDataAccessor().ExecuteScalar(strSql, null);
                int ri = 0;
                if (obje != DBNull.Value)
                {
                    ri = Convert.ToInt32(obje);
                }
                return Misc.GetSpTextById(ri, "StateID", "StateName", "sp_ssrstate");
            }
        }
        #endregion 

        #region 历史查询相关...
        private string _lastTempHeapTable = string.Empty;
        /// <summary>
        /// 获取最后建立的临时表名
        /// </summary>
        public string LastTempHeapTable
        {
            get
            {
                return _lastTempHeapTable;
            }
        }
        static IDbConnection _conn;
        public static IDbConnection Conn
        {
            get
            {
                if (_conn == null)
                {
                    _conn = DbConnectionFactory.DefaultFactory.CreateConnection(micsunConfiguration.ConnectionString);
                }

                return _conn;
            }
        }
        public static void ExecuteQuery(string sql)
        {
            IDbCommand cmd = DbCommandFactory.DefaultFactory.CreateCommand(sql,Conn);

            if(cmd.Connection.State==ConnectionState.Closed)
			{
				cmd.Connection.Open();				
			}
            cmd.ExecuteNonQuery();
            
        }
        /// <summary>
        /// 建立以TempTableName为表名的内存临时表
        /// </summary>
        /// <param name="TempTableName">将建立的临时表名称</param>
        /// <returns>成功建立为true,否则为false</returns>
        public void CreateTempHeapTable(string TempTableName)
        {
            try
            {
                string strCommandText = "CREATE TEMPORARY TABLE " + TempTableName + " TYPE = HEAP (SELECT * FROM t_supervise where 1=2);";
                ExecuteQuery(strCommandText);
            }
            finally
            {
                _lastTempHeapTable = TempTableName;
            }
        }

        public void InsertFilesDataToTempHeapTable(string[] fileNames, string TempTableName)
        {
            for (int i = 0; i < fileNames.Length; i++)
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(fileNames[i]);
                string fileNme = fi.Name;
                int ls = fileNames[i].LastIndexOf('\\');
                string finName = fileNames[i].Substring(0, ls) + "\\\\" + fileNme;
                string strCommandText = "LOAD DATA INFILE '" + finName + "' into TABLE " 
                    + TempTableName + " FIELDS TERMINATED BY '\\t' ENCLOSED BY '\"'";
                ExecuteQuery(strCommandText);
            }

        }
        /// <summary>
        /// 删除临时表中数据
        /// </summary>
        /// <param name="TempTableName"></param>
        public void DeleteTempTableData(string TempTableName)
        {
            string strCommandText = "delete from " + TempTableName;
            ExecuteQuery(strCommandText);
        }
        /// <summary>
        /// 删除最后使用的临时表
        /// </summary>
        public static void DropLastTempHeapTable(string tempTableName)
        {
            if (tempTableName!= null && tempTableName != string.Empty)
            {
                string strCommandText = "DROP TABLE IF EXISTS " + tempTableName + ";";
                ExecuteQuery(strCommandText);
            }

        }
        #endregion
    }
}
